from flask import *
from pymysql import *

# 声明蓝图
bookBP = Blueprint(name="book", import_name=__name__)
# 连接数据库
db_connect = Connect(host="101.37.25.239", port=3306, user="root", password="123456", database='bms')


@bookBP.route("/")
def index():
    return render_template("index.html")


# 查询全部列表数据
@bookBP.route("/list")
def list():
    cursor = db_connect.cursor()
    sql_str_list = ''' select * from book; '''
    cursor.execute(sql_str_list)
    data = cursor.fetchall()
    datas = []
    for item in data:
        s = {}
        s["bid"] = item[0]
        s["name"] = item[1]
        s["price"] = item[2]
        s["summary"] = item[3]
        s["quantity"] = item[4]
        datas.append(s)
    cursor.close()
    return datas


# 添加页面以及 添加图书接口
@bookBP.route("/add", methods=["GET", "POST"])
def add():
    if request.method == "GET":
        return render_template("add.html")
    else:

        name = request.values.get("name")
        price = request.values.get("price")
        summary = request.values.get("summary")
        quantity = request.values.get("quantity")

        sql_str_add = f''' insert into book(name, price, summary, quantity) 
                            values({name},{price},{summary},{quantity}) ;'''
        cursor = db_connect.cursor()
        cursor.execute(sql_str_add)
        db_connect.commit()
        cursor.close()
        return redirect("/")


# 修改图书信息
@bookBP.route("/change/<bid>", methods=["GET", "POST"])
def change(bid):
    if request.method == "GET":
        return render_template("change.html")
    else:
        name = request.values.get("name")
        price = request.values.get("price")
        summary = request.values.get("summary")
        quantity = request.values.get("quantity")
        sql_str_change = f''' update book set 
                    name={name}, price={price}, summary={summary}, quantity={quantity} 
                    where bid = {bid}; '''
        cursor = db_connect.cursor()
        cursor.execute(sql_str_change)
        db_connect.commit()
        cursor.close()
        return redirect("/list")


# 用来返回修改信息时的回显数据
@bookBP.route("/changeData/<bid>")
def changeData(bid):
    cursor = db_connect.cursor()
    sql_change = f'''select * from book where bid = {bid}'''
    cursor.execute(sql_change)
    item = cursor.fetchone()
    data = {}
    data["bid"] = item[0]
    data["name"] = item[1]
    data["price"] = item[2]
    data["summary"] = item[3]
    data["quantity"] = item[4]
    cursor.close()
    return data


# 删除图书信息
@bookBP.route("/delete/<bid>")
def delete(bid):
    curosr = db_connect.cursor()
    sql_delete = f''' delete from book where bid = {bid} '''
    curosr.execute(sql_delete)
    db_connect.commit()
    curosr.close()
    return redirect("/list")


# 搜索接口
@bookBP.route("/search")
def search():
    wd = f'%{request.values.get("wd")}%'
    cursor = db_connect.cursor()
    sql = f''' select * from book where name like {wd} or summary like {wd}'''
    cursor.execute(sql)
    data = cursor.fetchall()
    datas = []
    for item in data:
        s = {}
        s["bid"] = item[0]
        s["name"] = item[1]
        s["price"] = item[2]
        s["summary"] = item[3]
        s["quantity"] = item[4]
        datas.append(s)
    cursor.close()
    return datas
